NLphoneCheck is a FilemakerPro tool for Dutch phone number verification, formatting and conversion from the old form to the new. It contains no Mac programming, so it runs whenever FilemakerPro v2 runs.
Upgrade to NLphoneCheck2.0 is required for all FMPRv3.x users.
Due to a Claris bug, calculation fields in v2.x do not convert correctly to FMPRv3.x!
The present version is merely a port, it does not use any of the new featurtes of FMPRv3.
1 Cleaning up your database files before installation
1.1 NLphoneCheck assumes phone numbers as entered for dialing in the Netherlands. All phone numbers beginning with + or 00, (international) will not be changed. Begin 09 is changed to begin 00. Prepend non-NLnumbers appropriately.
06-numbers and the new 1xx, 0800, 090x Euro numbers are copied unchanged.
1.2 American style letters in telephone numbers will be lost. Change these to digits (A.4) before conversion, or use the comment possibility (1.8).
The letter O cannot be used for the digit 0, this is flagged as an error.
1.3 You should add missing local area codes. Since the changes may depend on both area code and subscriber number, I cannot restore missing area codes for you. It is posssible to make a special localized version on demand. See Appendix A.4 for do it yourself.
1.4 Original input formatting is irrelevant. NLphoneCheck will strip irrelevant ( )'s, - 's, spaces and all other stray characters before a ; .
The converted number will be in the new standard formats
0XX-XXX XX XX, or 0XXX-XX XX XX. or (0XX) XXX XX XX depending on the variant (E or N) you use. Different variants are easily made.
1.5 More than one phone number in a field cannot be handled, except as comment. Make a second (and a third) etc phone number field, and split your fields. If you have had the forethought to separate the numbers by a recognizable symbol, (for examle / or b.g.g.), this can be done automatically, see appendix A.
1.6 Filemaker's repeating fields are not sufficiently supported by FilemakerPro itself to allow automatic conversion. Not my fault! Repeating field contents must first be separated into single fields. See appendix A for a simple automatic method of doing this.
1.7 Errors are signaled in TelNew, and if desired also in a separate field in plain language, but this takes time and space.
E: O/o ≠ 0 F! : Letter O or o , replace by digit 0
E: 3 d <<<< F! : Three digits too many in the input.
E: 1 d >>>> F! : One digit short.
E: (0XXX) X = ?? F! : Nonexistent area code (and/or first subscriber digits).
E: (0XX) 0 F! : First digit of subscriber number cannot be zero.
The E, F, and d for digit to allow lists to be searched for errors.
1.8 An escape for comments, annotations, and aberations has been added:
If the number contains the sequence ; (that is semicolon followed by a space), everything following ; will be ignored as part of the phone number and copied in the result, also when it contains digits. So (030) 666666; Tst 66. yields the result (030) 266 66 66; Tst 66. The ; is deleted when there is no phone number, ; comment goes to comment. See the supplied examples.
1.9 In case of errors (usually too short) also check the PTT file OUD-WIJZ.TXT for previous number changes that may have to be applied manually by you, such as 010-xx xx xx --> 010-4xx xx xx.
2 Batch Check, Format, and Conversion
2.0 The files JJLcheckData and NLphoneCheck should be in the same folder, or you have to open the lookup file separately. The old data file PTTconvPro cannot be used, it is not compatible with NLphoneCheck. The datafile is password protected against changes. An empty password will do for conversion Click OK for limited access (not cancel!) when asked.
2.1 Converting single numbers and manual input.
Make a copy of your NLphoneCheck file and open it. Create a new record from the Edit menu or with Cmd-N. Enter the phone number and press Enter (on the numeric keypad) or Tab, not Return! The phone number will be converted if possible, or you get an error message (see 1.7). Formatting is not necessary, it is sufficient to enter the digits, the leading zero may be omitted.
The numeric keypad can be used for fast entering. The leading zero of the area code may be omitted. The single layout is fastest.
Press Cmd-N and enter the next number to be converted. (You can also create lots of records beforehand, and jump records with Cmd-Tab. Create a TEXT file with 0 return, 0 return ....... ty times and import it.)
The end result will be a list which can be viewed in the list layout, imported into a database, or printed.
2.2 Converting lists.
If you have a list of phone numbers (typed in a word processor and saved as text-only, or exported from any spreadsheet or database as tab-separated text) in the format number, return, number, return,........., list conversion is possible.
Clean up the list as needed. Open (a copy of) NLphoneCheck and import the list (by choosing import from the file menu) into the field TelOld. The data are not converted yet! To convert put the cursor in the field TelOld and choose Relookup from Filemaker's Edit(v2.x) or Mode(v3.x) menu. In FMPRv3.x it is also possible (and faster) to perform the lookup during import by checking the checkbox in the import dialog.
The converted numbers appearing in the field TelNew can be printed, viewed, or re-exported where they came from.
For your convenience I added an empty text field, called Id@ntLab@l. It is not changed in any way. You can import any unique identifier, (serial nr, customer nr, name hash, etc) to be extra sure your phone numbers do not get mixed up between records, or use it for comments.
2.3 Converting FilemakerPro files.
Clean up the data in the file if necessary. Work on a copy, in case you do not like the conversion! No information can be lost, converted data will appear in a new field.
Open your file and be sure you are the only user having access during conversion. Select the records you want to convert. Choose Define Fields (Cmd-Shift-D) from the Select menu. Create a new text field, name it TelNew or whatever. Click Done and close the file.
Open (a copy of) NLphoneCheck and import your phone number field from your database file into the field TelOld in NLphoneCheck.
The data are not converted yet! Next put the cursor in the field TelOld and select Relookup from the Edit(v2.x) or Mode(v3.x) menu. Wait until the conversion is complete. In FMPRv3.x it is also possible (and faster) to perform the lookup during import by checking the checkbox in the import dialog.
Close the file, reopen your database and import TelNew. You can also make your TelNew (temporarily) a lookup field and relookup TelNew when TelOld matches your old phone number. You can also do a relookup on Id@ntL@bel, if you have it filled.
2.4 Worst case time: Count on about 6 sec for a single conversion and 6+6 sec/rec for import+relookup on a MacPlus with sys7.x. An SE/30 or old II-series Mac is four times faster already, and on recent Macs the single number delay should be unnoticable.
3 Direct conversion, copying the definitions. (password needed)
You can copy the field definitions into your own files, and have immediate conversion of all new phone numbers entered in your database files. (or copy your field definitions into a copy of my file and then rename it appropriately, whichever is less work)
You need the password to access the field definitions, which you can obtain after registration. You can then copy the field definitions to your datebase.
You need to define 3 new fields. This is the unavoidable minimum, given the limitations of FMPR.
Open (a copy or clone of) your database file, be sure to have adequate backup. If you have a really big file work on a clone and reimport and relookup your data overnight to save waiting for calculations.
Open NLphoneCheck with the password.
Choose define fields under the Edit Menu (Cmd-Shift-D). Select TelOld and rename it to your field name, or create a (text or calculation) field TelOld in your file. ( Filemaker renames automaticaly in calc fields, saving you the trouble.)
Select the field @#1 and click options, select the whole formula (Cmd-A)
and copy (Cmd-C) the field definition.
Go to your own file, do Cmd-Shift-D and create the same calc field there. Select options and do Paste (Cmd-V). Click OK, Done.
(Localization: In FMPR NL it is possible that ; may have been used in formulae as a separator instead of , . You then have to use the files with a ; in the filename in the folder variant.)
@#1 is a calculation field of type text equal to the relevant part of the input, to be looked up in the data file.
@#2 is the lookup field of type text. Set @#2 to lookup the value of E2 in JJLcheckData, when the value in E1 matches a new entry in @#1. Do not forget to set in case of no match Use 0 , correct conversion depends on this! Do not use the file PTTconvPro supplied with earlier versions!
(If you have a lot of data in the file pause here and do the relookup on TelOld now (coffeebreak), or else you have to wait twice for recomputation. v2.x only)
Finally TelNew computes the new phone number and formats it if you have set the calc result to be of type text. You obtain the bare number without the leading zero if you set the calculation result to be of type number.
Create a new calc field in your file with a suitable name and copy the formula into it as explained above.
Be careful with changes to the formulae, some of the subtleties are not immediately obvious. Output formatting is easily changed, the relevant line contains "(0" and ") ".
If you have more than one phone field you have to duplicate the conversion formulae, renaming the fields in a convenient way, @#11 ,@#21, TelNew1, or whatever, then copy into them your file.
It is also possible to define TelOld as an extra calc field set equal to each of your phone fields in turn. Then you can convert more than one phone field in batches without duplication of the conversion machinery.
To force conversion of existing or imported records after taking over the formulae do a Relookup on TelOld. New entries in TelOld will convert immediately in TelNew without the need for a relookup.
Old and new numbers can be mixed in the input field, the formula recognizes both and converts old to new only when necessary.
Appendix A
A.1 Separating multiple entries in one field with a recognizable separator (/, b.g.g., etc) can be effected by exporting the repeating field contents as tab-separated text. Create as many new fields as needed to accomodate the pieces. Next open the exported text file in an editor such as BBEdit, MS Word, or any other program capable of full ASCII substitution, and the separators can be replaced with tabs. (tab = ^t in Word, \t in BBEdit with grep on.) Save the file as text (usu auto if text to begin with) and import the contents into the new fields.
A.2 Filemaker's repeating fields are similar, in tab-separated output the repetitions are separated by (non-printing) ASCII 29 characters, which apear as squares, Replace these with tabs and re-import into separate fields. (Word: ^29 goes to ^t, BBEdit: paste the squares into the find box, replace all by \t with grep on)
A.3 The converse is also easy. Export all fields you want to join as tab-separated text, replace the tabs by ASCII 29's for a repeating field or whatever for a single field, and reimport.
A.4 Adding missing area codes or international prefixes is easy with BBEdit. Just select and export the relevant field contents, prepend the area codes with the appropriate BBEdit extension, and reimport. It can also be done in FMPR with a suitably defined calculation field. TelOld becomes If(Plaats="Amsterdam" and Length(TextToNum(YourTel))=7 , "(020) "&YourTel,YourTel) or something like it.
